﻿CREATE PROCEDURE [acms].[Post_GetBySiteIdAndPath]
	@SiteId int, 
	@Path varchar(255),
	@SiteVersion DateTime
AS
	declare @tmp DateTime
	
	set @tmp = acms.GetPublishDate(@SiteVersion)

	if(@tmp is null and @SiteVersion is not null)
		return

	if(Right(@Path, 1) = '/')
		set @Path = Substring(@Path, 1, Len(@Path) - 1)
	
	SELECT a.*, b.ParentPath, b.PublishDate as SiteVersion
	FROM Post_View a
		inner join PostPositions b on a.Id = b.PostId and (b.PublishDate = @tmp or (b.PublishDate is null and @tmp is null))
	WHERE 
		SiteId = @SiteId
		and isNull(b.ParentPath, '') + a.Name COLLATE DATABASE_DEFAULT = @Path
RETURN 0;
